#! /usr/bin/env ruby

require "rubygems"
require "csv"
require "uri"
require "net/http"
require "SQLite3"

def get_coordinates_for(northing, easting)
  url = URI.parse('http://valdemar.kms.dk/cgi-trf/trfcrd')
  post_data  = {"fromsys"  => "utm32_euref89",
                "calcsys"  => "geo_euref89",
                "trfmain"  => "1",
                "y"        => "#{northing}",
                "x"        => "#{easting}",
                "geounit"  => "11",
                "mtrunit"  => "41",
                "textfile" => "aaappa4Z2",
                "fromnmb"  => "5",
                "calcnmb"  => "11"}
                
  response = Net::HTTP.post_form(url, post_data)
  
  # regular expression to match fields in html response where the coordinates are located
  reg = Regexp.new("(value=\"){1}([0-9]+\s[0-9]+\s[0-9]+).*sx")
  matches = response.body.scan(reg)
  
  latitude = coordinate_in_degrees_from_deg_min_sec(matches[0][1])
  longitude = coordinate_in_degrees_from_deg_min_sec(matches[1][1])
  
  [latitude, longitude]
end

def coordinate_in_degrees_from_deg_min_sec(coordinate_in_deg_min_sec)
  deg, min, sec = coordinate_in_deg_min_sec.split(" ")
  # puts "deg:#{deg}, min:#{min}, sec:#{sec}"
  (deg.to_f + min.to_f/60 + sec.to_f/3600)
end

###### Parsing ######

# open database
db = SQLite3::Database.new( "ttdb.sql" )
# delete all station locations in the database
db.execute("delete from Stations")

# open file for parsing
File.open("Stogsstationer.csv") do |f|
  f.each_line do |line|
    # parse one line
    parsed_line = CSV::parse(line, ";")
    station_name = parsed_line[0][1].gsub("\"", "").strip
    # remove " st" from station name if present - this is to make the name equal to the name used in the time table
    station_name.gsub!(" st", "")
    # get station coordinate
    easting, northing = parsed_line[0][2,3]
    
    # convert coordinates to degrees
    latitude, longitude = get_coordinates_for(northing, easting)
    
    # print
    puts station_name + ", northing: #{northing} => latitude: #{latitude}, easting: #{easting} => longitude: #{longitude}"
    
    # save location in the database
    sql = "insert into Stations values (\"#{station_name}\", #{latitude}, #{longitude})"
    puts sql
    db.execute(sql)
  end
end
